package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.logging.LogFactory;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.util.DBUtil;

/**
 * 供应商合并申请表（ERP） <br>
 * 通过供应商编码，获取供应商名称
 * 
 * @author Administrator
 * 
 */
public class ERPMergerOperateDao {

	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(ERPMergerOperateDao.class);

	private static final String QUERY_PROVIDER_NAME_FROM_UAP = "select v.cvenname from fy_supplier_vendor v where v.cvencode = ? and nvl(v.dr, 0) = 0 and (v.denddate is null or to_date(v.denddate, 'yyyy-mm-dd') >= sysdate)";
	private static final String QUERY_PROVIDER_NAME_FROM_ERP = "select b.vendor_name 供应商名称, a.vendor_site_code 地名  FROM APPS.PO_VENDOR_SITES_ALL A,  APPS.AP_VENDORS_V B,  INV.MTL_PARAMETERS MP where a.vendor_id = b.vendor_id  and a.org_id = mp.organization_id and a.inactive_date is null and b.vendor_number = ? and mp.organization_id = ?";

	private static final String ERROR_RESULT = "{success : false, data : '访问数据库发生异常，请稍后重试，\\n或者联系OA系统管理员。'}";
	private static final String NULL_RESULT = "{success : false, data : '未查询到该供应商编码对应的供应商\\n或该供应商已被停用'}";
	private static final String ERROR_STR = "{success : false,data : '《供应商合并申请表（ERP）》查询供应商名称发生异常，\\n请重试，或者联系OA系统管理员'}";

	private static final String QUERY_OA_FORM_BY_NO = "select u.oa_number, u.syb, u.oa_number_sequence,  u.erp_bm, u.erp_sm, u.from_site, u.uap_bm, u.uap_sm, u.to_site,((  select count(*) from APPS.PO_VENDORs a,     APPS.PO_VENDOR_SITES_ALL b,     apps.AP_DUPLICATE_VENDORS_ALL c where c.duplicate_vendor_id=b.vendor_id   and c.duplicate_vendor_site_id=b.vendor_site_id  and b.vendor_id=a.vendor_id and  a.segment1 = u.erp_bm and u.from_site = b.vendor_site_code and c.org_id = decode(u.syb,'福耀玻璃工业集团股份有限公司',101  ,'福清万达玻璃',102  ,'福清包边',104  ,'福清巴士玻璃',103  ,'万盛配件玻璃',243  ,'上海巴士玻璃',329  ,'上海轿车玻璃',323  ,'北京汽车玻璃',283  ,'广州汽车玻璃',182  ,'湖北汽车玻璃',443  ,'福清机械制造',403  ,'郑州汽车玻璃',483  ,'重庆汽车玻璃',263  ,'长春巴士玻璃',305  ,'长春轿车玻璃',303  ,'集团总办管理处',363  ,'广州佛山汽车玻璃',405	,'福州福耀模具科技有限公司',643	,'沈阳汽车玻璃',584	,'烟台福耀',583,'武汉福耀',803)  ) ) from apps.FYG_AP_VENDORS_unite u where u.oa_number = ? order by u.oa_number_sequence asc";
	private static final String NULL_QUERY_BY_OA_NUMBER_RESULT = "{success : false, data : '未查询到该OA单据编号对应的表单数据，原因：\\n1、单据编号输入错误；\\n2、单据编号对应的表单流程还未结束。'}";
	private static final String ERROR_QUERY_BY_OA_NUMBER_RESULT = "{success : false,data : '查询OA单据编号对应的《供应商合并申请表（ERP）》数据发生异常，\\n请重试，或者联系OA系统管理员'}";

	private static final String ERROR_YIHEBING = "{success : false,msg : '检验目标供应商是否已被合并发生异常，\\n请重试或者联系OA系统管理员。'}";

	/**
	 * 验证供应商是否已被合并
	 */

	/**
	 * 从ERP数据库查询供应商名称
	 * 
	 * @param code
	 * @param pk_corp
	 * @return
	 */
	public static String queryProviderNameFromERP(String code, String ou_id,
			String name) {
		String result = null;

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getERP_DB_CONFIG());

			pst = conn.prepareStatement(QUERY_PROVIDER_NAME_FROM_ERP);

			pst.setString(1, code);
			pst.setString(2, ou_id);

			rs = pst.executeQuery();
			boolean first = true;
			JSONObject jo = new JSONObject();
			while (rs.next()) {
				if (first) {
					jo.put("success", true);
					jo.put("name", name == null ? rs.getString(1) : name);
					first = false;
				}
				jo.append("address", rs.getString(2));
			}
			if (first) {
				result = NULL_RESULT;
			} else {
				result = jo.toString();
			}

		} catch (Exception e) {
			e.printStackTrace();
			log.error(ERROR_STR, e);
		} finally {
			DBUtil.close(rs, pst, conn);
		}

		return result != null ? result : ERROR_RESULT;
	}

	/**
	 * 从UAP数据库查询供应商名称
	 * 
	 * @param code
	 * @param pk_corp
	 * @return
	 */
	public static String queryProviderNameFromUAP(String code, String ou_id) {

		String result = null;
		boolean success = false;

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());


			pst = conn.prepareStatement(QUERY_PROVIDER_NAME_FROM_UAP);

			pst.setString(1, code);

			rs = pst.executeQuery();

			if (rs.next()) {
				result = rs.getString(1);
				success = true;
			} else {
				result = NULL_RESULT;
			}

		} catch (Exception e) {
			e.printStackTrace();
			log.error(ERROR_STR, e);
		} finally {
			DBUtil.close(rs, pst, conn);
		}

		return result != null ? (success ? queryProviderNameFromERP(code,
				ou_id, result) : result) : ERROR_RESULT;
	}

	/**
	 * 从ERP数据库查询已提交的ERP供应商合并数据
	 * 
	 * @param code
	 * @param pk_corp
	 * @return
	 */
	public static String queryFormInfo(String number) {

		String result = null;

		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getERP_DB_CONFIG());


			pst = conn.prepareStatement(QUERY_OA_FORM_BY_NO);

			pst.setString(1, number);

			rs = pst.executeQuery();

			String oa_number = null;
			String syb = null;
			JSONObject jo = new JSONObject();
			int count = 0;
			while (rs.next()) {
				// select u.oa_number,u.syb, u.oa_number_sequence, u.erp_bm,
				// u.erp_sm, u.from_site, u.uap_bm, u.uap_sm, u.to_site from
				// apps.FYG_AP_VENDORS_unite u where u.oa_number = ? and u.flag
				// = 'Y' order by u.oa_number_sequence asc
				if (oa_number == null) {
					oa_number = rs.getString(1);
				}
				if (syb == null) {
					syb = rs.getString(2);
				}
				JSONObject row = new JSONObject();
				row.put("serial", rs.getString(3));
				row.put("erp_bm", rs.getString(4));
				row.put("erp_sm", rs.getString(5));
				row.put("from_site", rs.getString(6));
				row.put("to_uap_bm", rs.getString(7));
				row.put("to_uap_sm", rs.getString(8));
				row.put("to_site", rs.getString(9));
				row.put("merger", rs.getInt(10) > 0);
				jo.append("rows", row);
				count++;
				success = true;
			}

			if (!success) {
				result = NULL_QUERY_BY_OA_NUMBER_RESULT;
			} else {
				if (count > 0) {
					jo.put("success", success);
					jo.put("oa_number", oa_number);
					jo.put("syb", syb);
					result = jo.toString();
				} else {
					result = "{success : false, data:'该单据编号对应的《供应商合并申请表（ERP）》包含的供应商合并项都已被取消。'}";
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
			log.error(ERROR_QUERY_BY_OA_NUMBER_RESULT, e);
			result = ERROR_QUERY_BY_OA_NUMBER_RESULT;
		} finally {
			DBUtil.close(rs, pst, conn);
		}

		return result != null ? result : ERROR_QUERY_BY_OA_NUMBER_RESULT;
	}

	/**
	 * 确认目标供应商是否被合并
	 * 
	 * @param code
	 * @param pk_corp
	 * @return
	 */
	public static String yiHeBing(String corp, String[] codeArray,
			String[] siteArray) {

		String result = null;

		boolean success = true;

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getERP_DB_CONFIG());

			StringBuilder sb = new StringBuilder()
					.append("select u.erp_sm, u.erp_bm, u.from_site, u.oa_number from apps.FYG_AP_VENDORS_unite u where u.ou_id = ? and ( ");
			for (int i = 0, length = codeArray.length; i < length; i++) {
				if (i != 0) {
					sb.append(" or ");
				}
				sb.append(" u.erp_bm = ? and u.from_site = ? ");
			}
			sb.append(")");
			pst = conn.prepareStatement(sb.toString());
			sb = null;

			pst.setString(1, corp);
			for (int i = 0, length = codeArray.length; i < length; i++) {
				pst.setString(2 * i + 2, codeArray[i]);
				pst.setString(2 * i + 3, siteArray[i]);
			}
			rs = pst.executeQuery();
			JSONObject jo = new JSONObject();
			sb = new StringBuilder().append("以下供应商已被合并，不能再次合并:\n");
			while (rs.next()) {
				success = false;
				sb.append("供应商“").append(rs.getString(1)).append("(")
						.append(rs.getString(2)).append(")”").append("   地点：")
						.append(rs.getString(3)).append("    OA单据编号：")
						.append(rs.getString(4)).append("\n");
			}
			jo.put("success", success);
			if (!success) {
				jo.put("data", sb.toString());
			}
			result = jo.toString();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(ERROR_YIHEBING, e);
			result = ERROR_YIHEBING;
		} finally {
			DBUtil.close(rs, pst, conn);
		}

		return result != null ? result : ERROR_YIHEBING;
	}
}
